﻿			 -----------				BÁO CÁO KHÁCH HÀNG ACTIVE: DATABASE : ACTIVE_CUSTOMER_1			

	DECLARE @TDATE1 VARCHAR(8)=(select convert(nvarchar(8), dateadd(m,-4,dateadd(d,1,MAX_DATE_ENDMONTH)),112) from DATE_EM_REPORT)
	DECLARE @TDATE2 VARCHAR(8) = (select convert(nvarchar(8), dateadd(m,-1,dateadd(d,1,MAX_DATE_ENDMONTH)),112) from DATE_EM_REPORT)
	DECLARE @TDATE3 VARCHAR(8)=  (select MAX_DATE_ENDMONTH from DATE_EM_REPORT) 
	DECLARE @TDATE4 VARCHAR(8)=(select convert(nvarchar(7), convert(date,dateadd(m,-5,MAX_DATE_ENDMONTH)),120)from DATE_EM_REPORT)
	DECLARE @TDATE5 VARCHAR(8)=(select convert(nvarchar(7), convert(date,dateadd(m,-4,MAX_DATE_ENDMONTH)),120)from DATE_EM_REPORT)
	DECLARE @TDATE6 VARCHAR(8)=(select convert(nvarchar(7), convert(date,dateadd(m,-3,MAX_DATE_ENDMONTH)),120)from DATE_EM_REPORT)
	DECLARE @TDATE7 VARCHAR(8)=(select convert(nvarchar(7), convert(date,dateadd(m,-2,MAX_DATE_ENDMONTH)),120)from DATE_EM_REPORT)
	DECLARE @TDATE8 VARCHAR(8)=(select convert(nvarchar(7), convert(date,dateadd(m,-1,MAX_DATE_ENDMONTH)),120)from DATE_EM_REPORT)
	DECLARE @TDATE9 VARCHAR(8) =(select convert(nvarchar(7), convert(date,MAX_DATE_ENDMONTH),120)from DATE_EM_REPORT) 
	
	DECLARE @TDATE16 VARCHAR(20)=(select MAX_MONTH from DATE_EM_REPORT)
	declare @monthTransaction1 nvarchar(6) = (select convert(nvarchar(6), convert(date,MAX_DATE_ENDMONTH),112)from DATE_EM_REPORT)
	declare @monthTransaction2 nvarchar(6) =(select convert(nvarchar(6), convert(date,dateadd(m,-1,MAX_DATE_ENDMONTH)),112)from DATE_EM_REPORT)
	declare @monthTransaction3 nvarchar(6) =(select convert(nvarchar(6), convert(date,dateadd(m,-2,MAX_DATE_ENDMONTH)),112)from DATE_EM_REPORT)
	declare @monthTransaction4 nvarchar(6) =(select convert(nvarchar(6), convert(date,dateadd(m,-3,MAX_DATE_ENDMONTH)),112)from DATE_EM_REPORT)
	--select * from TBL_CUSTOMER
------------------ ACTIVE CUSTOMER
   EXEC ('IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = ''ACTIVE_CUSTOMER_4_MONTH'' AND TYPE = ''U'')
       DROP TABLE ACTIVE_CUSTOMER_4_MONTH')
EXEC('
SELECT A.CIF,A.CUS_NAME,A.LEGAL_ID,A.DAO,A.BRANCH_ID AS BRANCH_CODE,B.BRANCH_NAME_SME AS BRANCH_NAME,
REGION =CASE	WHEN B.ZONE_ID_SME = ''VUNG 01'' THEN ''REGION 1''
				WHEN B.ZONE_ID_SME = ''VUNG 02'' THEN ''REGION 2''
				WHEN B.ZONE_ID_SME = ''VUNG 03'' THEN ''REGION 3''
				WHEN B.ZONE_ID_SME = ''VUNG 04'' THEN ''REGION 4''
				WHEN B.ZONE_ID_SME = ''VUNG 05'' THEN ''REGION 5''
				WHEN B.ZONE_ID_SME = ''VUNG 06'' THEN ''REGION 6''
				WHEN B.ZONE_ID_SME = ''VUNG 07'' THEN ''REGION 7''
				ELSE ''UNALLOCATED''
				END
			 
INTO ACTIVE_CUSTOMER_4_MONTH
FROM TBL_CUSTOMER A
LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE B
ON A.BRANCH_ID=B.BRANCH_ID
WHERE CONVERT(DATE,CUS_OPEN_DATE) <= '''+@TDATE3+'''
AND SEGMENT = ''SMES''
')

PRINT('1')

EXEC('
ALTER TABLE ACTIVE_CUSTOMER_4_MONTH
ADD PRO_CASA VARCHAR(4),PRO_TD VARCHAR(4),PRO_LOAN VARCHAR(4)
,BAL_TD VARCHAR(4),BAL_TD_OTHER VARCHAR(4),BAL_CASA VARCHAR(4),BAL_LOAN VARCHAR(4) 
')

EXEC('
UPDATE ACTIVE_CUSTOMER_4_MONTH
SET PRO_CASA = ''1''
FROM ACTIVE_CUSTOMER_4_MONTH A,
(SELECT CUSTOMER_ID AS CIF 
	FROM [Transaction].[dbo].[SME_TRANSACTION]
	where yearmonth in('''+@monthTransaction1+''','''+@monthTransaction2+''','''+@monthTransaction3+''','''+@monthTransaction4+''')
)B
WHERE A.CIF =B.CIF
')
PRINT('2')
---- UPDATE PRODUCT TERM DEPOSIT
 EXEC('
UPDATE ACTIVE_CUSTOMER_4_MONTH
SET PRO_TD =''1''
FROM ACTIVE_CUSTOMER_4_MONTH A,
(
SELECT DISTINCT CIF FROM SERVER74.BICDATA.DBO.DPTB_MASTER
WHERE PRODUCT_NAME = ''2. TERM DEPOSIT''
AND SEGMENT = ''SMES''
AND OPENNING_DATE BETWEEN '''+@TDATE1+''' AND '''+@TDATE3+'''
 ) B
 WHERE A.CIF =B.CIF
')
 --- UPDATE PRODUCT LOAN

 --select top 1 * FROM SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP]
 PRINT('4')
 EXEC('
 UPDATE ACTIVE_CUSTOMER_4_MONTH
 SET PRO_LOAN =''1''
 FROM ACTIVE_CUSTOMER_4_MONTH A,
 (
 SELECT * FROM (
 SELECT DISTINCT CIF FROM SERVER74.BICDATA.DBO.LNTB_DISBURSEMENT 
 WHERE SEGMENT = ''SMES''
 AND DIST_DATE BETWEEN '''+@TDATE1+''' AND '''+@TDATE3+'''
 AND CIF IS NOT NULL
 UNION 
 SELECT DISTINCT CIF FROM SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP]
 WHERE [year_month] IN (
 --'''+@TDATE4+''','''+@TDATE5+''',
 '''+@monthTransaction1+''','''+@monthTransaction2+''','''+@monthTransaction3+''','''+@monthTransaction4+''')
 AND CIF IS NOT NULL
 UNION
 SELECT DISTINCT CUST_ID AS CIF FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH 
 WHERE [MONTH] IN (
 --'''+@TDATE4+''','''+@TDATE5+''',
 '''+@TDATE6+''','''+@TDATE7+''','''+@TDATE8+''','''+@TDATE9+''')
 AND CUST_ID IS NOT NULL
 ) B
    )B
 WHERE A.CIF =B.CIF
 ')
PRINT('3')
 EXEC(' 
 UPDATE ACTIVE_CUSTOMER_4_MONTH
 SET BRANCH_NAME = ''UNALLOCATED''
 WHERE REGION =''UNALLOCATED''
 ')

  EXEC('
  IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = ''tam_inactive'' AND TYPE = ''U'')
       DROP TABLE tam_inactive

 SELECT CIF,COUNT(ACCTNO) AS ACCTNO,COUNT(WITHDRAW_DATE) AS [WITHDRAW_DATE]
INTO tam_inactive
 FROM SERVER74.BICDATA.DBO.DPTB_MASTER
WHERE SEGMENT <>''KHCN'' AND
CIF IN (SELECT CIF FROM ACTIVE_CUSTOMER_4_MONTH)
GROUP BY CIF	  ')

EXEC('
UPDATE 	ACTIVE_CUSTOMER_4_MONTH
SET PRO_CASA = NULL , PRO_TD= NULL
FROM ACTIVE_CUSTOMER_4_MONTH A,( SELECT * FROM tam_inactive WHERE  ACCTNO = WITHDRAW_DATE) B
WHERE A.CIF =B.CIF
	  ')

EXEC('
ALTER TABLE ACTIVE_CUSTOMER_4_MONTH
ADD TT_KH VARCHAR(4) ')

EXEC('
 UPDATE ACTIVE_CUSTOMER_4_MONTH
SET TT_KH = ''0''
WHERE PRO_CASA IS NULL AND PRO_LOAN IS NULL AND PRO_TD IS NULL
')

EXEC('
UPDATE ACTIVE_CUSTOMER_4_MONTH
SET TT_KH = ''1''
WHERE TT_KH IS NULL
')

 --select * from ACTIVE_CUSTOMER_4_MONTH_20160731 where tt_kh = 0
 --drop table TBL_CM_ALERT_INACTIVE
 
	--DECLARE @TDATE9 VARCHAR(8) =(select convert(nvarchar(7), convert(date,MAX_DATE_ENDMONTH),120)from DATE_EM_REPORT) 
 --DROP TABLE tam_inactive
 delete from TBL_CM_ALERT_INACTIVE
 
declare @date_update_active nvarchar(100)= (select max(report_date) from SERVER12.TRANGLT9.DBO.[ACTIVE_CUSTOMER_DAILY_REPORT])

 insert TBL_CM_ALERT_INACTIVE
 SELECT c.*, d.BRANCH_CODE_SME, d.BRANCH_NAME_SME, d.REGION
 FROM server12.tranglt9.[dbo].[ACTIVE_CUSTOMER_DAILY_REPORT] A, ACTIVE_CUSTOMER_4_MONTH B, TBL_CUSTOMER C, TBL_BRANCH d
 WHERE a.report_date = @date_update_active and A.CIF = B.CIF AND A.Status='ACTIVE'
 AND B.TT_KH=0 AND C.CIF=A.CIF and c.BRANCH_ID=d.BRANCH_ID

update b
set b.date_data = a.BUSINESS_DATE
from (select distinct business_date from TBL_CM_ALERT_INACTIVE) a,  TBL_DATE_BUSINESS b
where b.name_table = 'TBL_CM_ALERT_INACTIVE'

 --select top 10 * from server12.tranglt9.[dbo].[ACTIVE_CUSTOMER_PTKD]
 --SELECT * FROM TBL_BRANCH WHERE BRANCH_ID= 'VN0010231'
 --SELECT * FROM TBL_CM_ALERT_INACTIVE WHERE CIF ='1216230'

 --SELECT * FROM ANHCP.CEO_PERFORMANCE.[dbo].[branchoff] WHERE BRANCH_CODE='VN0010231'

 
-- delete from [DBCustomer].dbo.TBL_CM_ALERT_INACTIVE
--insert  [DBCustomer].dbo.TBL_CM_ALERT_INACTIVE
--select * from TBL_CM_ALERT_INACTIVE

-- EXEC('IF EXISTS
--      (SELECT *
--         FROM [SERVER12].[SMECustomer360].DBO.SYSOBJECTS O
--        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CM_ALERT_INACTIVE'')
--        delete from [SERVER12].[SMECustomer360].dbo.TBL_CM_ALERT_INACTIVE')

-- delete from  [SERVER12].[SMECustomer360].dbo.TBL_CM_ALERT_INACTIVE

--insert [SERVER12].[SMECustomer360].dbo.TBL_CM_ALERT_INACTIVE
--select * from TBL_CM_ALERT_INACTIVE


--select * from tbl_hr where USER_NAME = 'nhudp'
